﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Caching;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SharePoint;
using TNO.Education.Helpers;

namespace TNO.Education.SearchResult
{
    public partial class SearchResultUserControl : UserControl
    {
        #region Constant

        const string ListUrl = "{0}?i={1}&p={2}&l=1";
        const string DetailUrl = "{0}?i={1}&p={2}";
        const int PageSize = 10;
        const int CacheMinutes = 3;

        #endregion

        #region Properties

        struct Student
        {
            public int Id;
            public string Code;
            public string Name;
            public string University;
            public string UniversityName;
            public string Industry;
            public string Group;

            public float? Subject1;
            public float? Subject2;
            public float? Subject3;
            public float? Subject4;
            public float? Subject5;
            public float? Subject6;
            public float? Total;

            //public float? StdMark;

            public int UniSeq;
            //public int? UniPoint;
            public int UniTotalRecords;
            public int UniEqualsToMe;
            public int UniGreaterThanMe;

            public int IndSeq;
            //public int? IndPoint;
            public int IndTotalRecords;
            public int IndEqualsToMe;
            public int IndGreaterThanMe;

            public DataTable RangeTable;
        }

        private string ConnectionString = System.Configuration.ConfigurationManager.
                ConnectionStrings["TuyenSinhConnectionString"].ConnectionString;

        private int Year { get { return DateTime.Today.Year; } }

        /// <summary>
        /// Get the base url
        /// </summary>
        protected string BaseUrl
        {
            get
            {
                int endIndex = SPContext.Current.Web.Url.IndexOf("?");
                if (endIndex != -1)
                    return SPContext.Current.ListItemServerRelativeUrl.Substring(0, endIndex + 1);
                else
                    return SPContext.Current.ListItemServerRelativeUrl;
            }
        }

        /// <summary>
        /// List by the university
        /// </summary>
        private bool ListAll
        {
            get { return Page.Request.QueryString["l"] == "1"; }
        }

        #endregion

        #region Events

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataTable tableCategory;
                object obj = HttpContext.Current.Cache[this.BaseUrl + "Category"];
                lock (this)
                {
                    if (obj == null)
                    {
                        tableCategory = GetUniversityList();
                        HttpContext.Current.Cache.Add(this.BaseUrl + "Category", tableCategory, null, DateTime.Now.AddSeconds(30), TimeSpan.Zero, CacheItemPriority.Default, null);
                    }
                    else
                    {
                        tableCategory = (DataTable)obj;
                    }
                }

                ddlUniversity.DataSource = tableCategory;
                ddlUniversity.DataTextField = "Title";
                ddlUniversity.DataValueField = "ID";
                ddlUniversity.DataBind();

                string code = Page.Request.QueryString["SBD"];
                string university = Page.Request.QueryString["u"];

                this.txtName.Text = code ?? this.txtName.ToolTip;
                this.ddlUniversity.SelectedValue = university;

                bool isDetails = !string.IsNullOrEmpty(Page.Request.QueryString["i"]);
                if (isDetails)
                {
                    int pageNumber = string.IsNullOrEmpty(Page.Request.QueryString["p"]) ? -1 : Convert.ToInt32(Page.Request.QueryString["p"]);

                    LayoutDetails(Convert.ToInt32(Page.Request.QueryString["i"]), ListAll, pageNumber);
                }
                else
                {
                    using (HtmlHelper htmlHelper = new HtmlHelper())
                    {
                        //DataRow[] rows = GetNewUniversity(tableCategory);

                        //if (rows.Length > 0)
                        //{
                        //    using (htmlHelper.RenderTag(HtmlTag.P))
                        //    {
                        //        htmlHelper.AddClass("left");
                        //        using (htmlHelper.RenderTag(HtmlTag.Div))
                        //        {
                        //            htmlHelper.AddAttribute("alt", "diem thi moi");
                        //            htmlHelper.AddAttribute("src", "/_layouts/images/TNO.Education/New.gif");
                        //            htmlHelper.WriteLine(HtmlTag.Img);
                        //        }
                        //        htmlHelper.AddClass("left");
                        //        using (htmlHelper.RenderTag(HtmlTag.Div))
                        //        {
                        //            htmlHelper.AddStyle("font-weight: bolder");
                        //            htmlHelper.WriteLine(HtmlTag.Div, "Các trường mới có điểm:");
                        //            using (htmlHelper.RenderTag(HtmlTag.Div))
                        //            {
                        //                htmlHelper.AddStyle("display: inline");
                        //                using (htmlHelper.RenderTag(HtmlTag.Ul))
                        //                {
                        //                    for (int i = 0; i < rows.Length; i++)
                        //                    {
                        //                        htmlHelper.WriteLine(HtmlTag.Li, rows[i][Fields.University.Title].ToString());
                        //                    }
                        //                }
                        //            }
                        //        }
                        //    }
                        //    htmlHelper.AddClass("clear");
                        //    htmlHelper.WriteLine(HtmlTag.P);
                        //}

                        htmlHelper.AddStyle("margin-top: 10px");
                        using (htmlHelper.RenderTag(HtmlTag.P))
                        {
                            using (htmlHelper.RenderTag(HtmlTag.Div))
                            {
                                htmlHelper.AddStyle("font-weight: bolder");
                                using (htmlHelper.RenderTag(HtmlTag.Div))
                                {
                                    htmlHelper.WriteLine(@"Đã có <b style='color: red'>" + tableCategory.Rows.Count + "</b> trường công bố điểm thi Đại học cao đẳng 2013.");
                                    using (htmlHelper.RenderTag(HtmlTag.Span))
                                    {
                                        htmlHelper.AddAttribute("alt", "diem thi moi");
                                        htmlHelper.AddAttribute("src", "/_layouts/images/TNO.Education/New.gif");
                                        htmlHelper.WriteLine(HtmlTag.Img);
                                    }
                                }
                            }
                        }

                        this.pnlResult.Controls.Clear();
                        this.pnlResult.Controls.Add(new LiteralControl(htmlHelper.ToString()));
                    }
                }
            }

        }

        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);

            if (!base.IsPostBack)
            {
                // Block cookie less visit attempts
                if (IsFirstVisit())
                {
                    if (!ActionValidator.IsValid(ActionValidator.ActionTypeEnum.FirstVisit)) Response.End();
                }
                else
                {
                    if (!ActionValidator.IsValid(ActionValidator.ActionTypeEnum.Revisit)) Response.End();
                }
            }
            else
            {
                // Limit number of postbacks
                if (!ActionValidator.IsValid(ActionValidator.ActionTypeEnum.Postback)) Response.End();
            }
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            pnlResult.Controls.Clear();

            if (string.IsNullOrEmpty(ddlUniversity.SelectedValue))
            {
                using (HtmlHelper htmlHelper = new HtmlHelper())
                {
                    htmlHelper.AddClass("error-tcdt");
                    htmlHelper.WriteLine(HtmlTag.Div, "Chọn trường cần tuyển sinh.");
                    pnlResult.Controls.Add(new LiteralControl(htmlHelper.ToString()));
                }
            }
            else if (string.IsNullOrEmpty(txtName.Text)
                || txtName.Text == txtName.ToolTip)
            {
                using (HtmlHelper htmlHelper = new HtmlHelper())
                {
                    htmlHelper.AddClass("error-tcdt");
                    htmlHelper.WriteLine(HtmlTag.Div, "Số báo danh không được bỏ trống.");
                    pnlResult.Controls.Add(new LiteralControl(htmlHelper.ToString()));
                }
            }
            else if (txtName.Text.Trim().Length == 0)
            {
                using (HtmlHelper htmlHelper = new HtmlHelper())
                {
                    htmlHelper.AddClass("error-tcdt");
                    htmlHelper.WriteLine(HtmlTag.Div, "Số báo danh nhập không hợp lệ.");
                    pnlResult.Controls.Add(new LiteralControl(htmlHelper.ToString()));
                }
            }
            else
            {
                string key = this.BaseUrl + ddlUniversity.SelectedValue + txtName.Text;
                object obj = HttpContext.Current.Cache[key];
                lock (this)
                {
                    if (obj == null)
                    {
                        obj = GetStudentsList(ddlUniversity.SelectedValue, txtName.Text);
                        HttpContext.Current.Cache.Add(key, obj, null, DateTime.Now.AddMinutes(CacheMinutes), TimeSpan.Zero, CacheItemPriority.Default, null);
                    }
                }

                LayoutSearchResult(obj as DataTable, -1);
            }
        }

        #endregion

        #region Methods

        private void LayoutSearchResult(DataTable table, int id)
        {
            using (HtmlHelper htmlHelper = new HtmlHelper())
            {
                if (table.Rows.Count > 0)
                {
                    htmlHelper.AddAttribute("cellspacing", "1");
                    htmlHelper.AddClass("grid-table");
                    using (htmlHelper.WriteTag(HtmlTag.Table))
                    {
                        using (htmlHelper.RenderTag("thead"))
                        {
                            using (htmlHelper.RenderTag(HtmlTag.Tr))
                            {
                                htmlHelper.AddAttribute("rowspan", "2");
                                htmlHelper.WriteLine(HtmlTag.Th, "STT");
                                htmlHelper.AddAttribute("rowspan", "2");
                                htmlHelper.WriteLine(HtmlTag.Th, "SBD");
                                htmlHelper.AddAttribute("rowspan", "2");
                                htmlHelper.WriteLine(HtmlTag.Th, "Họ và tên");
                                htmlHelper.AddAttribute("rowspan", "2");
                                htmlHelper.WriteLine(HtmlTag.Th, "Khối thi");
                                htmlHelper.AddAttribute("rowspan", "2");
                                htmlHelper.WriteLine(HtmlTag.Th, "Mã ngành");
                                htmlHelper.AddAttribute("colspan", "6");
                                htmlHelper.WriteLine(HtmlTag.Th, "Môn thi");
                                htmlHelper.AddAttribute("rowspan", "2");
                                htmlHelper.WriteLine(HtmlTag.Th, "Tổng điểm");
                                //htmlHelper.AddAttribute("rowspan", "2");
                                //htmlHelper.WriteLine(HtmlTag.Th, "Khu vực");
                                //htmlHelper.AddAttribute("rowspan", "2");
                                //htmlHelper.WriteLine(HtmlTag.Th, "+ Ưu tiên");
                                htmlHelper.AddAttribute("rowspan", "2");
                                htmlHelper.WriteLine(HtmlTag.Th, "Thông tin cần biết");
                            }
                            using (htmlHelper.RenderTag(HtmlTag.Tr))
                            {
                                htmlHelper.WriteLine(HtmlTag.Th, "Môn 1");
                                htmlHelper.WriteLine(HtmlTag.Th, "Môn 2");
                                htmlHelper.WriteLine(HtmlTag.Th, "Môn 3");
                                htmlHelper.WriteLine(HtmlTag.Th, "Môn 4");
                                htmlHelper.WriteLine(HtmlTag.Th, "Môn 5");
                                htmlHelper.WriteLine(HtmlTag.Th, "Môn 6");
                            }
                        }
                        using (htmlHelper.RenderTag("tbody"))
                        {
                            foreach (DataRow item in table.Rows)
                            {
                                if (id.Equals(Int32.Parse(item["ID"].ToString())))
                                {
                                    htmlHelper.AddClass("tr-selected");
                                }
                                using (htmlHelper.WriteTag(HtmlTag.Tr))
                                {
                                    htmlHelper.AddClass("td-01");
                                    htmlHelper.WriteLine(HtmlTag.Td, item[Fields.ExamResult.RowId].ToString());
                                    htmlHelper.AddClass("td-02");
                                    htmlHelper.WriteLine(HtmlTag.Td, item[Fields.ExamResult.SBD].ToString());
                                    htmlHelper.AddClass("td-03");
                                    htmlHelper.WriteLine(HtmlTag.Td, item[Fields.ExamResult.Hoten].ToString());
                                    htmlHelper.AddClass("td-04");
                                    htmlHelper.WriteLine(HtmlTag.Td, item[Fields.ExamResult.KhoiThi].ToString());
                                    htmlHelper.AddClass("td-05");
                                    htmlHelper.WriteLine(HtmlTag.Td, item[Fields.ExamResult.MaNganh].ToString());
                                    htmlHelper.AddClass("td-06");
                                    htmlHelper.WriteLine(HtmlTag.Td, GetDisplayMark(item[Fields.ExamResult.DM1]));
                                    htmlHelper.AddClass("td-07");
                                    htmlHelper.WriteLine(HtmlTag.Td, GetDisplayMark(item[Fields.ExamResult.DM2]));
                                    htmlHelper.AddClass("td-08");
                                    htmlHelper.WriteLine(HtmlTag.Td, GetDisplayMark(item[Fields.ExamResult.DM3]));
                                    htmlHelper.AddClass("td-09");
                                    htmlHelper.WriteLine(HtmlTag.Td, GetDisplayMark(item[Fields.ExamResult.DM4]));
                                    htmlHelper.AddClass("td-10");
                                    htmlHelper.WriteLine(HtmlTag.Td, GetDisplayMark(item[Fields.ExamResult.DM5]));
                                    htmlHelper.AddClass("td-11");
                                    htmlHelper.WriteLine(HtmlTag.Td, GetDisplayMark(item[Fields.ExamResult.DM6]));
                                    htmlHelper.AddClass("td-12");
                                    htmlHelper.WriteLine(HtmlTag.Td, GetDisplayMark(item[Fields.ExamResult.TongDiem]));
                                    //htmlHelper.AddClass("td-13");
                                    //htmlHelper.WriteLine(HtmlTag.Td, item[Fields.ExamResult.KhuVuc].ToString());
                                    //htmlHelper.AddClass("td-14");
                                    //htmlHelper.WriteLine(HtmlTag.Td, (Convert.ToSingle(item[Fields.ExamResult.TongDiem]) + GetAddMark(item[Fields.ExamResult.KhuVuc].ToString())).ToString());

                                    htmlHelper.AddClass("td-15");
                                    using (htmlHelper.RenderTag(HtmlTag.Td))
                                    {
                                        htmlHelper.AddAttribute("href", string.Format("{0}?i={1}", this.BaseUrl, item[Fields.ExamResult.Id]));
                                        htmlHelper.WriteLine(HtmlTag.A, "Chi tiết");
                                    }
                                }
                            }
                        }
                    }
                }
                else
                {
                    htmlHelper.AddClass("error-tcdt");
                    htmlHelper.WriteLine(HtmlTag.Div, "Không tìm thấy kết quả.");
                }
                pnlResult.Controls.Add(new Literal { Text = htmlHelper.ToString() });
            }
        }

        private void LayoutDetails(int id, bool listAll, int currentPage)
        {
            pnlResult.Controls.Clear();

            using (HtmlHelper htmlHelper = new HtmlHelper())
            {
                // Details
                int totalPages;

                Student student;
                string key = this.BaseUrl + '_' + id;
                object obj = HttpContext.Current.Cache[key];
                lock (this)
                {
                    if (obj == null && currentPage == -1)
                    {
                        student = GetStudentDetails(id, out currentPage, out totalPages);
                        HttpContext.Current.Cache.Add(key, student, null, DateTime.Now.AddMinutes(CacheMinutes), TimeSpan.Zero, CacheItemPriority.Default, null);
                    }
                    else
                    {
                        student = (Student)obj;
                        student.RangeTable = GetListDetails(student, listAll, ref currentPage, out totalPages);
                    }
                }

                htmlHelper.AddClass("ten-ts");
                htmlHelper.WriteLine(HtmlTag.Div, string.Format("Thông tin cần biết của thí sinh: <b>{0}</b>", student.Name.ToUpper()));
                htmlHelper.AddClass("info-details");
                using (htmlHelper.WriteTag(HtmlTag.Div))
                {
                    htmlHelper.AddClass("inside-details");
                    using (htmlHelper.WriteTag(HtmlTag.Div))
                    {
                        using (htmlHelper.RenderTag(HtmlTag.Div))
                        {
                            htmlHelper.AddClass("ten-ts");
                            htmlHelper.WriteLine(HtmlTag.Div, string.Format("SBD: <b>{0}</b> - <b>{1}</b>", student.Code, student.Name.ToUpper()));
                            htmlHelper.WriteLine(HtmlTag.Div, string.Format("Môn 1: <b>{0}</b>, Môn 2: <b>{1}</b>, Môn 3: <b>{2}</b>{3}{4}{5} - Tổng điểm: {6}", // - Điểm cộng: {7} - Tổng cộng: {8}",
                                student.Subject1, student.Subject2, student.Subject3, GetDisplayMark(student.Subject4, 4), GetDisplayMark(student.Subject5, 5), GetDisplayMark(student.Subject6, 6), student.Total)); //, student.AddMark, student.Total + student.AddMark));
                        }
                        using (htmlHelper.RenderTag(HtmlTag.Div))
                        {
                            htmlHelper.WriteLine(HtmlTag.Div, string.Format("Trường: {0}", student.UniversityName));
                            htmlHelper.WriteLine(HtmlTag.Div, string.Format("Ngành: {0}", student.Industry));
                            htmlHelper.WriteLine(HtmlTag.Div, string.Format("Khối: {0}", student.Group));
                        }
                        using (htmlHelper.RenderTag(HtmlTag.Div))
                        {
                            htmlHelper.WriteLine(HtmlTag.Div, string.Format("Thông tin thí sinh theo ngành <i>{0}</i> - Khối <i>{1}</i>:",
                                student.Industry, student.Group));
                            using (htmlHelper.RenderTag(HtmlTag.Ul))
                            {
                                //htmlHelper.WriteLine(HtmlTag.Li, string.Format("<b>Điểm chuẩn</b> năm {0}: {1}",
                                //        Year, student.StdMark));
                                //htmlHelper.WriteLine(HtmlTag.Li, string.Format("<b>Chỉ tiêu ngành</b> năm {0}: {1}",
                                //        Year, student.IndPoint));
                                //htmlHelper.WriteLine(HtmlTag.Li, string.Format("Thứ tự thí sinh theo ngành: {0}/{1}",
                                //       student.IndSeq, student.IndTotalRecords));
                                htmlHelper.WriteLine(HtmlTag.Li, string.Format("Số thí sinh theo <b>ngành cùng điểm</b> {0}: {1}",
                                       student.Total, student.IndEqualsToMe));
                                htmlHelper.WriteLine(HtmlTag.Li, string.Format("Số thí sinh theo <b>ngành hơn điểm</b>: {0}",
                                       student.IndGreaterThanMe));
                            }
                        }

                        using (htmlHelper.RenderTag(HtmlTag.Div))
                        {
                            htmlHelper.WriteLine(HtmlTag.Div, string.Format("Thông tin thí sinh theo trường <i>{0}</i>:",
                                student.UniversityName));
                            using (htmlHelper.RenderTag(HtmlTag.Ul))
                            {
                                //htmlHelper.WriteLine(HtmlTag.Li, string.Format("<b>Chỉ tiêu trường</b> năm {0}: {1}",
                                //       Year, student.UniPoint));
                                //htmlHelper.WriteLine(HtmlTag.Li, string.Format("Thứ tự thí sinh theo trường: {0}/{1}",
                                //       student.UniSeq, student.UniTotalRecords));
                                htmlHelper.WriteLine(HtmlTag.Li, string.Format("Số thí sinh theo <b>trường cùng điểm</b> {0}: {1}",
                                       student.Total, student.UniEqualsToMe));
                                htmlHelper.WriteLine(HtmlTag.Li, string.Format("Số thí sinh theo <b>trường hơn điểm</b>: {0}",
                                       student.UniGreaterThanMe));
                            }
                        }
                        htmlHelper.AddClass("note");
                        htmlHelper.WriteLine(HtmlTag.Div, "<b>Lưu ý: Thứ tự thí sinh theo ngành, trường được thống kê trên đây chỉ mang tính chất tham khảo.</b>");
                    }
                }

                //htmlHelper.WriteLine(HtmlTag.Div, string.Format("Với số điểm {0} bạn có thể trúng tuyển vào các trường sau đây:", student.Total));
                //using (htmlHelper.RenderTag(HtmlTag.Ul))
                //{
                //    for (int i = 1; i < 9; i++)
                //    {
                //        using (htmlHelper.RenderTag(HtmlTag.Li))
                //        {
                //            htmlHelper.AddAttribute("href", "#");
                //            htmlHelper.WriteLine(HtmlTag.A, string.Format("Trường Đại Học {0}", i));
                //        }
                //    }
                //}
                htmlHelper.AddClass(listAll ? "search-tab" : "search-tab selectedTab");
                htmlHelper.AddStyle("float: left");
                using (htmlHelper.RenderTag(HtmlTag.Div))
                {
                    if (listAll)
                    {
                        htmlHelper.AddAttribute("href", string.Format("{0}?i={1}", this.BaseUrl, id));
                        htmlHelper.WriteLine(HtmlTag.A, "Thứ tự theo ngành");
                    }
                    else
                        htmlHelper.WriteLine("Thứ tự theo ngành");
                }

                htmlHelper.AddClass(!listAll ? "search-tab" : "search-tab selectedTab");
                htmlHelper.AddStyle("float: left", "margin-left: 5px");
                using (htmlHelper.RenderTag(HtmlTag.Div))
                {
                    if (listAll)
                        htmlHelper.WriteLine("Thứ tự theo trường");
                    else
                    {
                        htmlHelper.AddAttribute("href", string.Format("{0}?i={1}&l=1", this.BaseUrl, id));
                        htmlHelper.WriteLine(HtmlTag.A, "Thứ tự theo trường");
                    }
                }

                pnlResult.Controls.Add(new Literal { Text = htmlHelper.ToString() });

                LayoutSearchResult(student.RangeTable, student.Id);

                Paging(student, currentPage, totalPages);
            }
        }

        private void Paging(Student student, int currentPage, int totalPages)
        {
            int startIndex, length;
            using (HtmlHelper htmlHelper = new HtmlHelper())
            {
                htmlHelper.AddClass("clear");
                htmlHelper.WriteLine(HtmlTag.Div);

                htmlHelper.AddId("grid-paging");
                using (htmlHelper.WriteTag(HtmlTag.Ul))
                {
                    if (currentPage < 4)
                    {
                        // TO DO: 1 -> Min(5, totalPages)
                        length = Math.Min(5, totalPages);
                        startIndex = 0;
                    }
                    else
                    {
                        // TO DO: < 2 3 4 -> Min(6, totalPages)
                        length = Math.Min(currentPage + 2, totalPages);
                        startIndex = length - 5;
                    }

                    string url = ListAll ? ListUrl : DetailUrl;

                    if (startIndex > 0)
                    {
                        // TO DO: |<
                        htmlHelper.AddClass("btn");
                        using (htmlHelper.RenderTag(HtmlTag.Li))
                        {
                            htmlHelper.AddAttribute("href", string.Format(url, this.BaseUrl, student.Id, 0));
                            htmlHelper.WriteLine(HtmlTag.A, "|<");
                        }
                        // TO DO: <
                        htmlHelper.AddClass("btn");
                        using (htmlHelper.RenderTag(HtmlTag.Li))
                        {
                            htmlHelper.AddAttribute("href", string.Format(url, this.BaseUrl, student.Id, startIndex - 1));
                            htmlHelper.WriteLine(HtmlTag.A, "<");
                        }
                    }

                    for (int i = startIndex; i < length; i++)
                    {
                        if (i == currentPage) htmlHelper.AddClass("page-item-selected");
                        using (htmlHelper.RenderTag(HtmlTag.Li))
                        {
                            htmlHelper.AddAttribute("href", string.Format(url, this.BaseUrl, student.Id, i));
                            htmlHelper.WriteLine(HtmlTag.A, string.Format("{0}", i + 1));
                        }
                    }

                    if (totalPages > length)
                    {
                        // TO DO: >
                        htmlHelper.AddClass("btn");
                        using (htmlHelper.RenderTag(HtmlTag.Li))
                        {
                            htmlHelper.AddAttribute("href", string.Format(url, this.BaseUrl, student.Id, length));
                            htmlHelper.WriteLine(HtmlTag.A, ">");
                        }
                        // TO DO: >|
                        htmlHelper.AddClass("btn");
                        using (htmlHelper.RenderTag(HtmlTag.Li))
                        {
                            htmlHelper.AddAttribute("href", string.Format(url, this.BaseUrl, student.Id, totalPages - 1));
                            htmlHelper.WriteLine(HtmlTag.A, ">|");
                        }
                    }
                }

                pnlResult.Controls.Add(new Literal { Text = htmlHelper.ToString() });
            }
        }

        private float GetAddMark(string location)
        {
            switch (location)
            {
                case "1":
                    return 1.5f;
                case "2":
                    return .5f;
                case "2NT":
                    return 1f;
                default: // NULL and 3
                    return 0;
            }
        }

        private bool IsFirstVisit()
        {
            if (Session["IsFirstVisit"] == null)
            {
                Session["IsFirstVisit"] = false;
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion

        #region Get Data Methods

        #region Obsolete methods
//        [Obsolete("This function is not used. You should use the GetListDetails function.")]
//        private Student GetStudentDetails1(string sbd, string university, bool listAll, out int currentPage, out int totalPages)
//        {
//            currentPage = 0;
//            Student student = new Student();
//            student.RangeTable = new DataTable();
//            string sqlQuery = @"
//                SELECT COUNT(1) AS TotalRecords FROM ThiSinh_DaiHoc 
//                    WHERE ID_Truong = @University
//                SELECT *, B.Title FROM (SELECT ROW_NUMBER() OVER(ORDER BY TongDiem DESC, HoTen) AS RowID, * FROM ThiSinh_DaiHoc 
//                    WHERE ID_Truong = @University) A LEFT JOIN TruongDaiHoc B ON A.ID_Truong = B.ID WHERE SBD = @SBD";
//            using (SqlConnection conn = new SqlConnection(ConnectionString))
//            {
//                conn.Open();
//                using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
//                {
//                    cmd.Parameters.AddWithValue("@University", university);
//                    cmd.Parameters.AddWithValue("@SBD", sbd);

//                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
//                    {
//                        DataSet dataSet = new DataSet();
//                        adapter.Fill(dataSet);

//                        student.UniTotalRecords = Convert.ToInt32(dataSet.Tables[0].Rows[0]["TotalRecords"]);

//                        var row = dataSet.Tables[1].Rows[0];

//                        student.UniSeq = Convert.ToInt32(row[Fields.ExamResult.RowId]);
//                        student.UniSeq = Convert.ToInt32(row[Fields.ExamResult.RowId]);
//                        student.University = row[Fields.ExamResult.MaTruong].ToString();
//                        student.UniversityName = row[Fields.ExamResult.TenTruong].ToString();
//                        student.Industry = row[Fields.ExamResult.MaNganh].ToString();
//                        student.Group = row[Fields.ExamResult.KhoiThi].ToString();
//                        student.Code = row[Fields.ExamResult.SBD].ToString();
//                        student.Name = row[Fields.ExamResult.Hoten].ToString();
//                        student.Subject1 = Convert.ToSingle(row[Fields.ExamResult.DM1]);
//                        student.Subject2 = Convert.ToSingle(row[Fields.ExamResult.DM2]);
//                        student.Subject3 = Convert.ToSingle(row[Fields.ExamResult.DM3]);
//                        student.Location = row[Fields.ExamResult.KhuVuc].ToString();
//                        student.AddMark = GetAddMark(student.Location);
//                        student.Total = Convert.ToSingle(row[Fields.ExamResult.TongDiem]);
//                    }

//                    sqlQuery = @"
//                        SELECT COUNT(1) AS TotalRecords FROM ThiSinh_DaiHoc 
//                            WHERE ID_Truong = @University
//                                    AND MaNganh = @Industry
//                        SELECT RowID FROM (SELECT ROW_NUMBER() OVER(ORDER BY TongDiem DESC, HoTen) AS RowID, * FROM ThiSinh_DaiHoc 
//                            WHERE ID_Truong = @University AND MaNganh = @Industry) A WHERE SBD = @SBD";
//                    cmd.CommandText = sqlQuery;
//                    cmd.Parameters.Add(new SqlParameter("@Industry", student.Industry));

//                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
//                    {
//                        DataSet dataSet = new DataSet();
//                        adapter.Fill(dataSet);

//                        student.IndTotalRecords = Convert.ToInt32(dataSet.Tables[0].Rows[0]["TotalRecords"]);
//                        student.IndSeq = Convert.ToInt32(dataSet.Tables[1].Rows[0]["RowID"]);
//                    }

//                    int totalRecords;
//                    if (listAll)
//                    {
//                        currentPage = student.UniSeq / PageSize;
//                        totalRecords = student.UniTotalRecords;
//                        totalPages = (int)Math.Ceiling((float)student.UniTotalRecords / PageSize);
//                    }
//                    else
//                    {
//                        currentPage = student.IndSeq / PageSize;
//                        totalRecords = student.IndTotalRecords;
//                        totalPages = (int)Math.Ceiling((float)student.IndTotalRecords / PageSize);
//                    }

//                    int startIndex = currentPage * PageSize + 1;
//                    int endIndex = Math.Min(totalRecords, startIndex + PageSize - 1);

//                    sqlQuery = @"
//                            SELECT COUNT(1) AS TotalRecords FROM ThiSinh_DaiHoc 
//                                WHERE ID_Truong = @University 
//                                    AND TongDiem = @Total
//                            SELECT COUNT(1) AS TotalRecords FROM ThiSinh_DaiHoc 
//                                WHERE ID_Truong = @University 
//                                    AND TongDiem > @Total
//                            SELECT COUNT(1) AS TotalRecords FROM ThiSinh_DaiHoc 
//                                WHERE ID_Truong = @University 
//                                    AND MaNganh = @Industry
//                                    AND TongDiem = @Total
//                            SELECT COUNT(1) AS TotalRecords FROM ThiSinh_DaiHoc 
//                                WHERE ID_Truong = @University 
//                                    AND MaNganh = @Industry
//                                    AND TongDiem > @Total
//                            SELECT * 
//                            FROM (
//                                SELECT ROW_NUMBER() OVER(ORDER BY TongDiem DESC, HoTen) AS RowID, * 
//                                    FROM ThiSinh_DaiHoc WHERE ID_Truong = @University 
//                                            AND (@Industry IS NULL OR MaNganh = @Industry)
//                            ) A WHERE RowID BETWEEN @StartIndex AND @EndIndex";
//                    cmd.CommandText = sqlQuery;
//                    cmd.Parameters.Clear();
//                    cmd.Parameters.AddWithValue("@University", student.University);
//                    cmd.Parameters.AddWithValue("@Industry", student.Industry);
//                    cmd.Parameters.AddWithValue("@Total", student.Total);
//                    cmd.Parameters.AddWithValue("@StartIndex", startIndex);
//                    cmd.Parameters.AddWithValue("@EndIndex", endIndex);

//                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
//                    {
//                        using (DataSet dataSet = new DataSet())
//                        {
//                            adapter.Fill(dataSet);
//                            student.UniEqualsToMe = Convert.ToInt32(dataSet.Tables[0].Rows[0]["TotalRecords"]);
//                            student.UniGreaterThanMe = Convert.ToInt32(dataSet.Tables[1].Rows[0]["TotalRecords"]);
//                            student.IndEqualsToMe = Convert.ToInt32(dataSet.Tables[2].Rows[0]["TotalRecords"]);
//                            student.IndGreaterThanMe = Convert.ToInt32(dataSet.Tables[3].Rows[0]["TotalRecords"]);
//                            student.RangeTable = dataSet.Tables[4];
//                        }
//                    }
//                }
//            }

//            return student;
//        }
        #endregion

        private Student GetStudentDetails(int id, out int currentPage, out int totalPages)
        {
            currentPage = 0;
            totalPages = 0;

            Student student = new Student();
            student.RangeTable = new DataTable();
            string sqlQuery = @"[pro_GetTruongDaiHoc]";
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Id", id);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    cmd.Parameters.AddWithValue("@CurrentPage", currentPage).Direction = ParameterDirection.Output;
                    cmd.Parameters.AddWithValue("@TotalPages", totalPages).Direction = ParameterDirection.Output;

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet dataSet = new DataSet();
                        adapter.Fill(dataSet);

                        student.IndSeq = Convert.ToInt32(dataSet.Tables[0].Rows[0][Fields.Proc.ThuTuNganh]);

                        student.UniTotalRecords = Convert.ToInt32(dataSet.Tables[1].Rows[0][Fields.Proc.HitCounter]);
                        student.IndTotalRecords = Convert.ToInt32(dataSet.Tables[2].Rows[0][Fields.Proc.HitCounter]);
                        student.UniEqualsToMe = Convert.ToInt32(dataSet.Tables[3].Rows[0][Fields.Proc.HitCounter]);
                        student.UniGreaterThanMe = Convert.ToInt32(dataSet.Tables[4].Rows[0][Fields.Proc.HitCounter]);
                        student.IndEqualsToMe = Convert.ToInt32(dataSet.Tables[5].Rows[0][Fields.Proc.HitCounter]);
                        student.IndGreaterThanMe = Convert.ToInt32(dataSet.Tables[6].Rows[0][Fields.Proc.HitCounter]);
                        //if (dataSet.Tables[7].Rows.Count > 0 && dataSet.Tables[7].Rows[0][Fields.Proc.Diem] != DBNull.Value)
                        //{
                        //    student.StdMark = Convert.ToSingle(dataSet.Tables[7].Rows[0][Fields.Proc.Diem]);
                        //}
                        //if (dataSet.Tables[8].Rows.Count > 0 && dataSet.Tables[8].Rows[0][Fields.Proc.ChiTieu] != DBNull.Value)
                        //{
                        //    student.UniPoint = Convert.ToInt32(dataSet.Tables[8].Rows[0][Fields.Proc.ChiTieu]);
                        //}
                        //if (dataSet.Tables[9].Rows.Count > 0 && dataSet.Tables[9].Rows[0][Fields.Proc.ChiTieu] != DBNull.Value)
                        //{
                        //    student.IndPoint = Convert.ToInt32(dataSet.Tables[9].Rows[0][Fields.Proc.ChiTieu]);
                        //}

                        var row = dataSet.Tables[10].Rows[0];

                        student.Id = Convert.ToInt32(row[Fields.ExamResult.Id]);
                        student.UniSeq = Convert.ToInt32(row[Fields.ExamResult.RowId]);
                        student.University = row[Fields.ExamResult.MaTruong].ToString();
                        student.UniversityName = row[Fields.ExamResult.TenTruong].ToString();
                        student.Industry = (row[Fields.ExamResult.MaNganh] == DBNull.Value) ? string.Empty : row[Fields.ExamResult.MaNganh].ToString();
                        student.Group = (row[Fields.ExamResult.KhoiThi] == DBNull.Value) ? string.Empty : row[Fields.ExamResult.KhoiThi].ToString();
                        student.Code = row[Fields.ExamResult.SBD].ToString();
                        student.Name = row[Fields.ExamResult.Hoten].ToString();
                        student.Subject1 = row[Fields.ExamResult.DM1] != DBNull.Value ? (float?)Convert.ToSingle(row[Fields.ExamResult.DM1]) : null;
                        student.Subject2 = row[Fields.ExamResult.DM2] != DBNull.Value ? (float?)Convert.ToSingle(row[Fields.ExamResult.DM2]) : null;
                        student.Subject3 = row[Fields.ExamResult.DM3] != DBNull.Value ? (float?)Convert.ToSingle(row[Fields.ExamResult.DM3]) : null;
                        student.Subject4 = row[Fields.ExamResult.DM4] != DBNull.Value ? (float?)Convert.ToSingle(row[Fields.ExamResult.DM4]) : null;
                        student.Subject5 = row[Fields.ExamResult.DM5] != DBNull.Value ? (float?)Convert.ToSingle(row[Fields.ExamResult.DM5]) : null;
                        student.Subject6 = row[Fields.ExamResult.DM6] != DBNull.Value ? (float?)Convert.ToSingle(row[Fields.ExamResult.DM6]) : null;
                        //student.Location = row[Fields.ExamResult.KhuVuc].ToString();
                        //student.AddMark = GetAddMark(student.Location);
                        student.Total = row[Fields.ExamResult.TongDiem] != DBNull.Value ? (float?)Convert.ToSingle(row[Fields.ExamResult.TongDiem]) : null;

                        student.RangeTable = dataSet.Tables[11];
                    }

                    currentPage = Convert.ToInt32(cmd.Parameters["@CurrentPage"].Value);
                    totalPages = Convert.ToInt32(cmd.Parameters["@TotalPages"].Value);
                }
            }

            return student;
        }

        private DataTable GetListDetails(Student student, bool listAll, ref int currentPage, out int totalPages)
        {
            student.RangeTable = new DataTable();
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                string sqlQuery = @"
                            SELECT * 
                            FROM (
                                SELECT ROW_NUMBER() OVER(ORDER BY TongDiem DESC, HoTen) AS RowID, * 
                                    FROM ThiSinh_DaiHoc WHERE ID_Truong = @University 
                                            AND ISNULL(MaNganh, '') = @Industry
                            ) A WHERE RowID BETWEEN @StartIndex AND @EndIndex";
                using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                {
                    int totalRecords;
                    if (listAll)
                    {
                        if (currentPage == -1) currentPage = (student.UniSeq - 1) / PageSize;
                        totalRecords = student.UniTotalRecords;
                        totalPages = (int)Math.Ceiling((float)student.UniTotalRecords / PageSize);
                    }
                    else
                    {
                        if (currentPage == -1) currentPage = (student.IndSeq - 1) / PageSize;
                        totalRecords = student.IndTotalRecords;
                        totalPages = (int)Math.Ceiling((float)student.IndTotalRecords / PageSize);
                    }

                    int startIndex = currentPage * PageSize + 1;
                    int endIndex = Math.Min(totalRecords, startIndex + PageSize - 1);

                    cmd.Parameters.AddWithValue("@Industry", student.Industry);
                    cmd.Parameters.AddWithValue("@University", student.University);
                    cmd.Parameters.AddWithValue("@StartIndex", startIndex);
                    cmd.Parameters.AddWithValue("@EndIndex", endIndex);

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        using (DataSet dataSet = new DataSet())
                        {
                            adapter.Fill(dataSet);
                            student.RangeTable = dataSet.Tables[0];
                        }
                    }
                }
            }

            return student.RangeTable;
        }

        private DataTable GetStudentsList(string university, string keyword)
        {
            DataTable table = new DataTable();
            string sqlQuery = @"
                SELECT ROW_NUMBER() OVER(ORDER BY TongDiem DESC, HoTen) AS RowID, *
                            FROM ThiSinh_DaiHoc
                            WHERE ID_Truong = @University
                            AND SBD = @SBD";
            float code;
            bool isSearchByCode = float.TryParse(keyword, out code);
            if (!isSearchByCode)
            {
                sqlQuery = @"
                SELECT ROW_NUMBER() OVER(ORDER BY TongDiem DESC, HoTen) AS RowID, *
                            FROM ThiSinh_DaiHoc
                            WHERE ID_Truong = @University";
                if (SearchResult._searchEndWidth)
                {
                    sqlQuery += " AND HoTen LIKE @Name";
                }
                else
                {
                    sqlQuery += " AND HoTen = @Name";
                }
            }

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                {
                    cmd.Parameters.AddWithValue("@University", university);
                    if (isSearchByCode)
                        cmd.Parameters.AddWithValue("@SBD", code);
                    else if (SearchResult._searchEndWidth)
                    {
                        cmd.Parameters.AddWithValue("@Name", '%' + keyword);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@Name", keyword);
                    }

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(table);
                    }
                }
            }

            return table;
        }

        private DataTable GetUniversityList()
        {
            DataTable table = new DataTable();
            string sqlQuery = @"
                SELECT RTRIM(ID) AS ID, Title FROM TruongDaiHoc 
                WHERE RTRIM(ID) IN (SELECT DISTINCT ID_Truong FROM ThiSinh_DaiHoc)
                ORDER BY Title";
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(table);
                    }
                }
            }

            return table;
        }

        private DataRow[] GetNewUniversity(DataTable table)
        {
            DataRow[] returnTable;
            object createdDate = table.Compute("MAX(CreatedDate)", string.Empty);
            if (createdDate == DBNull.Value)
            {
                returnTable = new DataRow[] { };
            }
            else
            {
                DateTime fromDate = Convert.ToDateTime(createdDate).Date;
                DateTime toDate = fromDate.AddDays(1);
                returnTable = table.Select(string.Format("CreatedDate >= '{0}' AND CreatedDate < '{1}'", fromDate, toDate), Fields.University.Title);
            }

            return returnTable;
        }

        private string GetDisplayMark(float? obj, int i)
        {
            return obj == null ? string.Empty : string.Format(", Môn {0}: <b>{1}</b>", i, obj.ToString());
        }

        private string GetDisplayMark(object obj)
        {
            return obj == DBNull.Value ? string.Empty : obj.ToString();
        }

        #endregion

    }
}
